package com.example.service.impl;

import ch.qos.logback.classic.Logger;
import com.example.entity.MaintUserAdded;
import com.example.ext.entity.ExtElevator;
import com.example.mapp.*;
import com.example.service.ExportService;
import com.example.util.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.json.JSONException;
import org.json.JSONObject;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by douyx on 2018/03/21.
 */
@Service
public class ExportServiceImpl implements ExportService {

    private final static Logger logger = (Logger) LoggerFactory.getLogger(ExportServiceImpl.class);

    @Autowired
    private ExportMapp exportMapp;

    @Autowired
    private ElevatorMapp elevatorMapp;

    @Autowired
    private MaintUserAddedMapp maintUserAddedMapp;

    @Autowired
    private PartyMapp partyMapp;

    @Autowired
    private Environment environment;

    @Autowired
    private RepairInfoMapp repairInfoMapp;

    @Autowired
    private SysUserMapp userMapp;

    /**
     * 导出维保记录
     *
     * @param map
     *            elevatorType: 1、曳引与强制驱动电梯 2、液压驱动电梯 3、杂物电梯 4、自动扶梯与自动人行道
     *            halfMonId: 半月保编号 elevatorId:电梯编号
     * @return map fileName:文件名 fileUrl:路径
     */
    @Override
    public Map<String, Object> exportMaintRecord(Map<String, Object> map) {
        Map<String, Object> result = new HashMap<>();
        String elevatorNum = map.get("elevatorNum") + "";
        String elevatorType = map.get("elevatorType") + "";
        String halfMonId = map.get("halfMonId") + "";

        // 1.获取保存路径，不存在则创建
        String path = environment.getProperty("upload", String.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateString = sdf.format(new Date());
        if (path.endsWith(File.separator)) {
            path = path + dateString + File.separator;
        } else {
            path = path + File.separator + dateString + File.separator;
        }
        File file1 = new File(path);
        if (!file1.exists()) {
            boolean flag = file1.mkdirs();
        }
        // 重现生成文件名，防止中文乱码
        String fileName = UUID.randomUUID() + ".xlsx";
        path = path + fileName;

        result.put("fileUrl", dateString + File.separator + fileName);
        result.put("fileName", "电梯维护保养记录表.xlsx");
        FileOutputStream fileOutStream = null;
        try {
            fileOutStream = new FileOutputStream(path);
            writeMaintRecordToFile(fileOutStream,elevatorNum,elevatorType,halfMonId);
        } catch (Exception e) {
            logger.error(e.getMessage(),e);
            return ResultUtil.returnError(e.getMessage(), null);
        }finally {
            if(fileOutStream != null){
                try{
                    fileOutStream.close();
                }catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return ResultUtil.returnSuccess("成功", result);
    }

    /**
     * 导出日常巡检记录 月度报表
     *
     * @param map
     *            month: 月份 yyyyMM elevatorId:电梯编号
     * @return map fileName:文件名 fileUrl:路径
     */
    @Override
    public Map<String, Object> exportDailyInspection(Map<String, Object> map) {
        Map<String, Object> result = new HashMap<>();

        // 1.获取保存路径，不存在则创建
        String path = environment.getProperty("upload", String.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateString = sdf.format(new Date());
        if (path.endsWith(File.separator)) {
            path = path + dateString + File.separator;
        } else {
            path = path + File.separator + dateString + File.separator;
        }
        File file1 = new File(path);
        if (!file1.exists()) {
            boolean flag = file1.mkdirs();
        }
        // 重现生成文件名，防止中文乱码
        String fileName = UUID.randomUUID() + ".xlsx";
        path = path + fileName;
        result.put("fileUrl", dateString + File.separator + fileName);
        result.put("fileName", "电梯运行日常巡查记录.xlsx");
        FileOutputStream fileOutStream = null;
        try {
            // 2.创建workbook和sheet
            fileOutStream = new FileOutputStream(path);
            writeDailyRecordToFile(fileOutStream,map);
        } catch (Exception e) {
            System.out.println(e);
            logger.error(e.getMessage());
            return ResultUtil.returnError(e.getMessage(), null);
        }finally {
            if(fileOutStream != null){
                try{
                    fileOutStream.close();
                }catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return ResultUtil.returnSuccess("成功", result);
    }

    /**
     * 导出日常巡检记录 月度报表  扶梯自动人行道
     *
     * @param map
     *            month: 月份 yyyyMM elevatorId:电梯编号
     * @return map fileName:文件名 fileUrl:路径
     */
    @Override
    public Map<String, Object> exportDailyFtInspection(Map<String, Object> map) {
        Map<String, Object> result = new HashMap<>();
        // 1.获取保存路径，不存在则创建
        String path = environment.getProperty("upload", String.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateString = sdf.format(new Date());
        if (path.endsWith(File.separator)) {
            path = path + dateString + File.separator;
        } else {
            path = path + File.separator + dateString + File.separator;
        }
        File file1 = new File(path);
        if (!file1.exists()) {
            boolean flag = file1.mkdirs();
        }
        // 重现生成文件名，防止中文乱码
        String fileName = UUID.randomUUID() + ".xlsx";
        path = path + fileName;
        result.put("fileUrl", dateString + File.separator + fileName);
        result.put("fileName", "自动扶梯/人行道日常巡查记录.xlsx");
        FileOutputStream fileOutStream = null;
        try {
            // 2.创建workbook和sheet
            fileOutStream = new FileOutputStream(path);
            writeDailyRecordToFile(fileOutStream,map);
        } catch (Exception e) {
            logger.error(e.getMessage());
            return ResultUtil.returnError(e.getMessage(), null);
        } finally {
            if(fileOutStream != null){
                try{
                    fileOutStream.close();
                }catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return ResultUtil.returnSuccess("成功", result);
    }

    /**
     * 导出电梯应急演练记录
     *
     * @param id: 电梯应急演练编号
     * @return map fileName:文件名 fileUrl:路径
     */
    @Override
    public Map<String, Object> exportRescueDrill(String id) {
        Map<String, Object> result = new HashMap<>();

        // 1.获取保存路径，不存在则创建
        String path = environment.getProperty("upload", String.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateString = sdf.format(new Date());
        if (path.endsWith(File.separator)) {
            path = path + dateString + File.separator;
        } else {
            path = path + File.separator + dateString + File.separator;
        }
        File file1 = new File(path);
        if (!file1.exists()) {
            boolean flag = file1.mkdirs();
        }
        // 重现生成文件名，防止中文乱码
        String fileName = UUID.randomUUID() + ".docx";
        // 填充完数据的临时xml
        String xmlTemp = System.getProperty("java.io.tmpdir") + UUID.randomUUID() + ".xml";
        File temp = new File(xmlTemp);
        path = path + fileName;
        result.put("fileUrl", dateString + File.separator + fileName);
        result.put("fileName", "电梯应急演练记录表.docx");
        try {
            // xml的文件名
            String xmlTemplate = "RescueDrill.xml";
            // docx的路径和文件名
            String docxTemplate = environment.getProperty("TEMPLATES",String.class)+ "/RescueDrill.docx";
            // 目标文件名
            String toFilePath = path;

            Writer w = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(temp), "UTF-8"));
            // 1.需要动态传入的数据
            Map<String, Object> tempMap = exportMapp.findRescueDrill(id);
            Map<String, String> data = new HashMap<>();
            Iterator it = tempMap.keySet().iterator();
            while (it.hasNext()) {
                String key = it.next()+"";
                data.put(underlineToCamel(key),tempMap.get(key) + "");
            }
            // 2.把map中的数据动态由freemarker传给xml
            XmlFill.process(xmlTemplate, data, w,environment);
            FileOutputStream fileOutputStream = new FileOutputStream(toFilePath);
            // 3.把填充完成的xml写入到docx中
            XmlToDocx.outDocx(temp, docxTemplate, fileOutputStream);
        } catch (Exception e) {
            logger.error(e.getMessage(),e);
            return ResultUtil.returnError(e.getMessage(), null);
        } finally {
            if(!temp.delete()){
                logger.warn("导出应急演练临时文件失败");
            }
        }
        return ResultUtil.returnSuccess("成功", result);
    }

    /**
     * 导出电梯应急救援记录
     *
     * @param id: 电梯应急救援编号
     * @return map fileName:文件名 fileUrl:路径
     */
    @Override
    public Map<String, Object> exportEmergencyRescue(String id) {
        Map<String, Object> result = new HashMap<>();

        // 1.获取保存路径，不存在则创建
        String path = environment.getProperty("upload", String.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateString = sdf.format(new Date());
        if (path.endsWith(File.separator)) {
            path = path + dateString + File.separator;
        } else {
            path = path + File.separator + dateString + File.separator;
        }
        File file1 = new File(path);
        if (!file1.exists()) {
            boolean flag = file1.mkdirs();
        }
        // 重现生成文件名，防止中文乱码
        String fileName = UUID.randomUUID() + ".docx";
        // 填充完数据的临时xml
        path = path + fileName;
        result.put("fileUrl", dateString + File.separator + fileName);
        result.put("fileName", "电梯应急救援记录表.docx");
        FileOutputStream fileOutputStream = null;
        try {

            // 目标文件名
            String toFilePath = path;
            fileOutputStream = new FileOutputStream(toFilePath);
            writeEmergencyRescueToFile(fileOutputStream,id);
        } catch (Exception e) {
            logger.error(e.getMessage(),e);
            return ResultUtil.returnError(e.getMessage(), null);
        }finally {
            if(fileOutputStream != null){
                try{
                    fileOutputStream.close();
                }catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return ResultUtil.returnSuccess("成功", result);
    }


    /**
     * 导出筛选出的主体单位名单
     *
     * @param map
     * @return map fileName:文件名 fileUrl:路径
     */
    @SuppressWarnings("rawtypes")
    @Override
    public Map<String, Object> exportRandomList(Map<String, Object> map) {
        Map<String, Object> result = new HashMap<>();

        // 1.获取保存路径，不存在则创建
        String path = environment.getProperty("upload", String.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateString = sdf.format(new Date());
        if (path.endsWith(File.separator)) {
            path = path + dateString + File.separator;
        } else {
            path = path + File.separator + dateString + File.separator;
        }
        File file1 = new File(path);
        if (!file1.exists()) {
            boolean flag = file1.mkdirs();
        }
        // 重现生成文件名，防止中文乱码
        String fileName = UUID.randomUUID() + ".xlsx";
        path = path + fileName;
        result.put("fileUrl", dateString + File.separator + fileName);
        result.put("fileName", "筛选主体单位名单.xlsx");
        FileOutputStream fileOutStream = null;
        try {
            // 2.创建workbook和sheet
            fileOutStream = new FileOutputStream(path);
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("主体单位名单");
            sheet.setColumnWidth(0, 10 * 256);
            sheet.setColumnWidth(1, 50 * 256);
            sheet.setColumnWidth(2, 50 * 256);
            sheet.setColumnWidth(3, 20 * 256);
            sheet.setColumnWidth(4, 20 * 256);
            sheet.setColumnWidth(5, 20 * 256);
            sheet.setColumnWidth(6, 20 * 256);
            XSSFCellStyle headStyle = wb.createCellStyle();
            headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
            headStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            headStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
            headStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
            headStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
            headStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线

            XSSFFont headFont = wb.createFont();
            headFont.setFontName("宋体");
            headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            headFont.setFontHeightInPoints((short) 14);
            headStyle.setFont(headFont);

            // 3.查询主体单位数据
            List<Map> data = partyMapp.queryRandomList(map);

            XSSFRow headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(30f); // 设置第一行高度
            String[] colList = {"id", "partyName", "partyAddr", "eleCount", "proCount", "userCount", "partyType"};
            String[] head = {"编号", "机构名称", "机构地址", "电梯数量", "项目数量", "作业人员数", "机构类型"};
            // 填充表头
            for (int i = 0; i < colList.length; i++) {
                XSSFCell headerCell = headerRow.createCell(i);
                headerCell.setCellValue(head[i]);
                headerCell.setCellStyle(headStyle);
            }
            // 填充内容
            for (int i = 1; i <= data.size(); i++) {
                XSSFRow bodyRow = sheet.createRow(i);
                for (int j = 0; j < colList.length; j++) {
                    XSSFCell bodyCell = bodyRow.createCell(j);
                    String tmpVal = String.valueOf(data.get(i - 1).get(colList[j]));
                    if ("id".equals(colList[j])) {
                        switch (tmpVal.length()) {
                            case 1:
                                tmpVal = "0000" + tmpVal;
                                break;
                            case 2:
                                tmpVal = "000" + tmpVal;
                                break;
                            case 3:
                                tmpVal = "00" + tmpVal;
                                break;
                            case 4:
                                tmpVal = "0" + tmpVal;
                                break;
                            default:
                                break;
                        }
                    } else if ("partyType".equals(colList[j])) {
                        switch (tmpVal) {
                            case "1":
                                tmpVal = "维保单位";
                                break;
                            case "2":
                                tmpVal = "使用单位";
                                break;
                            case "3":
                                tmpVal = "检验单位";
                                break;
                            case "4":
                                tmpVal = "监管单位";
                                break;
                            default:
                                break;
                        }
                    } else if ("null".equals(tmpVal)) {
                        tmpVal = "";
                    }
                    bodyCell.setCellValue(tmpVal);
                }
            }

            wb.write(fileOutStream);
        } catch (Exception e) {
            logger.error(e.getMessage());
            return ResultUtil.returnError(e.getMessage(), null);
        }finally {
            if(fileOutStream != null){
                try{
                    fileOutStream.flush();
                    fileOutStream.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        return ResultUtil.returnSuccess("成功", result);
    }

    /**
     * 批量导出作业记录
     * @param map
     * @return
     */
    @Override
    public Map<String, Object> batchExportWorkRecords(Map<String, Object> map) {
        Map<String, Object> result = new HashMap<>();
        // 1.获取压缩文件保存路径，不存在则创建
        String path = environment.getProperty("upload", String.class);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateString = sdf.format(new Date());
        if (path.endsWith(File.separator)) {
            path = path + dateString + File.separator;
        } else {
            path = path + File.separator + dateString + File.separator;
        }
        File file1 = new File(path);
        if (!file1.exists()) {
            boolean flag = file1.mkdirs();
        }
        // 重现生成文件名，防止中文乱码
        String fileName = UUID.randomUUID() + ".zip";
        path = path + fileName;
        File zipFile = new File(path);
        result.put("fileUrl", dateString + File.separator + fileName);
        try{
            //1、取得导出内容的入参
            List<Map<String,Object>> elevators = (List<Map<String,Object>>) map.get("elevatorList");
            String workType = (String) map.get("workType");
            String startDate = (String) map.get("startDate");
            String endDate = (String) map.get("endDate");
            if(elevators == null || elevators.size() == 0){
                return ResultUtil.returnError("电梯参数为空!", null);
            }
            if(workType == null || "".equals(workType)){
                return ResultUtil.returnError("作业类型参数为空!", null);
            }
            List<File> files = new ArrayList<>();

            //2、导出作业记录 1：维保记录 2：维修记录 3：巡检记录 4：救援记录 5：年度自检
            if("1".equals(workType)){
                for(Map<String,Object> elevator : elevators){
                    String elevatorNum = elevator.get("elevatorNum") + "";
                    String elevatorType = elevator.get("elevatorTypeId") + "";
                    Map<String,Object> params = new HashMap<>();
                    params.put("startDate",startDate);
                    params.put("endDate",endDate);
                    params.put("elevatorNum",elevatorNum);
                    params.put("elevatorType",elevatorType);
                    List<Map<String, Object>> halfMonIds = exportMapp.queryTractionHalfMonsByElevatorNum(params);
                    if(CollectionUtils.isEmpty(halfMonIds)){
                        continue;
                    }
                    String fileName0 = "电梯维护保养记录_" + elevator.get("outsideNum") + "_";
                    for(Map<String, Object> m : halfMonIds){
                        String halfMonId = m.get("halfMonId") + "";
                        String endtime = m.get("maintenanceDate") + "";
                        File file = new File(fileName0 + endtime + ".xlsx");
                        FileOutputStream fileOutStream = new FileOutputStream(file);
                        writeMaintRecordToFile(fileOutStream,elevatorNum,elevatorType,halfMonId);
                        files.add(file);
                    }
                }
                result.put("fileName", "电梯维护保养记录表.zip");
            }
            else if("2".equals(workType)){
                for(Map<String,Object> elevator : elevators){
                    Map<String,Object> params =new HashMap<>();
                    params.put("startDate",startDate);
                    params.put("endDate",endDate);
                    params.put("elevatorNum",elevator.get("elevatorNum") + "");
                    List<Map<String,Object>> repairInfos = exportMapp.queryRepairInfoByElevatorNum(params);
                    if(CollectionUtils.isEmpty(repairInfos)){
                        continue;
                    }
                    String fileName1 = "电梯故障维修记录_" + elevator.get("outsideNum") + "_";
                    for(Map<String,Object> repairInfo : repairInfos){
                        String resolveDate = repairInfo.get("resolveDate") + "";
                        File file = new File( fileName1 + resolveDate + ".docx");
                        writeRepairRecordToFile(file,elevator,repairInfo);
                        files.add(file);
                    }
                }
                result.put("fileName", "电梯故障维修记录表.zip");
            }
            else if("3".equals(workType)){ //巡检记录
                for(Map<String,Object> elevator : elevators){
                    map.put("elevatorType",elevator.get("elevatorTypeId") + "");
                    map.put("elevatorId",elevator.get("id") + "");
                    map.put("month",startDate);

                    String fileName0 = "电梯运行日常巡查记录_" + elevator.get("outsideNum") + "_";
                    File file = new File(fileName0 + startDate + ".xlsx");
                    FileOutputStream fileOutStream = new FileOutputStream(file);
                    writeDailyRecordToFile(fileOutStream,map);
                    files.add(file);
                }
                result.put("fileName", "电梯运行日常巡查记录表.zip");
            }
            else if("4".equals(workType)){
                for(Map<String,Object> elevator : elevators){
                    Map<String,Object> params = new HashMap<>();
                    params.put("startDate",startDate);
                    params.put("endDate",endDate);
                    params.put("elevatorNum",elevator.get("elevatorNum") + "");
                    List<Map<String, Object>> emergencyRescues = exportMapp.queryEmergencyRescueByElevatorNum(params);
                    String fileName1 = "应急救援记录表_" + elevator.get("outsideNum") + "_";

                    for(Map<String, Object> m : emergencyRescues){
                        String rescueEndTime = m.get("rescueEndTime") + "";
                        String id = m.get("id") + "";
                        File file = new File(fileName1 + rescueEndTime + ".docx");
                        FileOutputStream fileOutStream = new FileOutputStream(file);
                        writeEmergencyRescueToFile(fileOutStream,id);
                        files.add(file);
                    }
                }
                result.put("fileName", "应急救援记录表.zip");
            }
            else if("5".equals(workType)){
                for(Map<String,Object> elevator : elevators){
                    Map<String,Object> params = new HashMap<>();
                    params.put("startDate",startDate);
                    params.put("endDate",endDate);
                    params.put("elevatorNum",elevator.get("elevatorNum") + "");
                    List<Map<String, Object>> selfChecks = exportMapp.querySelfCheckByElevatorNum(params);
                    String fileName1 = "年度自检表_" + elevator.get("outsideNum") + "_";

                    for(Map<String, Object> m : selfChecks){
                        String checkTime = m.get("checkTime") + "";
                        File file = new File(fileName1 + checkTime + ".docx");
                        FileOutputStream fileOutStream = new FileOutputStream(file);

                        if(m.get("reportUrl") == null || "".equals(m.get("reportUrl"))){
                            continue;
                        }
                        writeSelfCheckRecordToFile(fileOutStream,m.get("reportUrl")+"",file);
                        files.add(file);
                    }
                }
                result.put("fileName", "年度自检报告表.zip");
            }
            if(files.size() == 0){
                return ResultUtil.returnSuccess("成功", "");
            }
            FileUploadUtil.zipFiles(files,zipFile);

        }catch (Exception e){
            logger.error(e.getMessage(),e);
            return ResultUtil.returnError(e.getMessage(), "");
        }
        return ResultUtil.returnSuccess("成功", result);
    }


    /**
     *
     * @param file
     * @param elevator
     * @param repairInfo
     * @throws Exception
     */
    private void writeRepairRecordToFile(File file,Map<String,Object> elevator,Map<String,Object> repairInfo) throws Exception {
        //组织维修参数
        Map<String,String> templateMap = new HashMap<>();
        templateMap.put("elevatorName",repairInfo.get("elevatorName") != null ? repairInfo.get("elevatorName") + "" : "");
        templateMap.put("buildAddr",repairInfo.get("buildAddr") != null ? repairInfo.get("buildAddr") + "" : "");
        templateMap.put("elevatorModel",repairInfo.get("elevatorModel") != null ? repairInfo.get("elevatorModel") + "" : "");
        templateMap.put("elevatorNum",repairInfo.get("elevatorNum") != null ? repairInfo.get("elevatorNum") + "" : "");
        templateMap.put("maintenancePartyName",repairInfo.get("maintenancePartyName") != null ? repairInfo.get("maintenancePartyName") + "" : "");
        templateMap.put("recorderName",repairInfo.get("recorderName") != null ? repairInfo.get("recorderName") + "" : "");
        templateMap.put("approveUserName",repairInfo.get("approveUserName") != null ? repairInfo.get("approveUserName") + "" : "");
        templateMap.put("applyTime",repairInfo.get("applyTime") != null ? repairInfo.get("applyTime") + "" : "");
        if(repairInfo.get("applyTime") != null){
            templateMap.put("applyTime",DateUtils.dataTimeToStr(repairInfo.get("applyTime") + "",DateUtils.parsePatterns[1]));
        }

        if(repairInfo.get("repairTime") != null){
            templateMap.put("repairTime",DateUtils.dataTimeToStr(repairInfo.get("repairTime") + "",DateUtils.parsePatterns[1]));
        }

        if(repairInfo.get("resolveDate") != null){
            templateMap.put("resolveDate",DateUtils.dataTimeToStr(repairInfo.get("resolveDate") + "",DateUtils.parsePatterns[1]));

        }
        templateMap.put("cost",repairInfo.get("cost") != null ? repairInfo.get("cost")+"" : "0");
        templateMap.put("repairCost",repairInfo.get("repairCost") != null ? repairInfo.get("repairCost") + "" : "0");
        templateMap.put("faultDescription",repairInfo.get("faultDescription") != null ? repairInfo.get("faultDescription") + "" : "");
        templateMap.put("reason",repairInfo.get("reason") != null ? repairInfo.get("reason") + "" : "");
        templateMap.put("replaceParts",repairInfo.get("replaceParts") != null ? repairInfo.get("replaceParts") + "" : "");
        templateMap.put("remarks",repairInfo.get("remarks") != null ? repairInfo.get("remarks") + "" : "");
        templateMap.put("isRepaired",repairInfo.get("isRepaired") != null ? repairInfo.get("isRepaired") + "" : "");
        if("1".equals(repairInfo.get("isRepaired"))){
            templateMap.put("isRepaired","√已修复 □未修复");
        }else{
            templateMap.put("isRepaired","□已修复 √未修复");
        }
        templateMap.put("unrepairedCause",repairInfo.get("unrepairedCause") != null ? repairInfo.get("unrepairedCause") + "": "");
        templateMap.put("repairUserName",repairInfo.get("repairUserName") != null ? repairInfo.get("repairUserName") + "" : "");
        templateMap.put("safeUserName",repairInfo.get("safeUserName") != null ? repairInfo.get("safeUserName") + "" : "");
        templateMap.put("maintTel",repairInfo.get("maintTel") != null ? repairInfo.get("maintTel") + "" : "");

        Map<String, Object> map = new HashMap<>();
        if(repairInfo.get("maintenancePartyId") != null){
            map.put("roleName", "%管理人员%");
            map.put("partyId", repairInfo.get("maintenancePartyId"));
            List<String> users = userMapp.getManagerUserName(map);
            if(users != null && users.size() > 0){
                templateMap.put("maintenanceUser",users.get(0) != null ? users.get(0) + "" : "");
            }
        }

        if(repairInfo.get("userdPartyId") != null){
            map.put("roleName", "%管理人员%");
            map.put("partyId", repairInfo.get("userdPartyId"));
            List<String> users = userMapp.getManagerUserName(map);
            if(users != null && users.size() > 0){
                templateMap.put("UseUser",users.get(0) != null ? users.get(0) + "" : "");
            }
        }

        String docxStr = "";
        String applyType = repairInfo.get("applyType") + "";
        if("1".equals(applyType)){
            docxStr =  "/maintenanceRecordTable1.docx";
        }else {
            docxStr = "/maintenanceRecordTable2.docx";
        }

        XmlFill.poiExportNewWord(file,templateMap,null,environment,docxStr);

    }

    /**
     * 年度自检的导出
     */
    private void writeSelfCheckRecordToFile(FileOutputStream fileOutStream, String reportUrl,File file){
        FileInputStream ips = null;
        try {
            //获取图片存放路径
            String filePath = environment.getProperty("upload",String.class) + File.separator + reportUrl;
            ips = new FileInputStream(new File(filePath));

            //读取文件流
            int len = 0;
            byte[] buffer = new byte[1024 * 10];
            while ((len = ips.read(buffer)) != -1){
                fileOutStream.write(buffer,0,len);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(fileOutStream != null){
                try{
                    fileOutStream.flush();
                    fileOutStream.close();

                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if(ips != null){
                try{
                    ips.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 日常巡检的导出
     * @param fileOutStream
     * @param map
     * @throws Exception
     */
    private void writeDailyRecordToFile(FileOutputStream fileOutStream,Map<String, Object> map) throws Exception{
        XSSFWorkbook wb = new XSSFWorkbook();
        String elevatorType = map.get("elevatorType") + "";
        String elevatorId = map.get("elevatorId") + "";
        String month = map.get("month") + "";

        XSSFSheet sheet = null;
        Map<String, String> fields = new HashMap<>();
        if("4".equals(elevatorType) || "6".equals(elevatorType)){
            sheet = wb.createSheet("自动扶梯人行道日常巡查记录");
            fields = getFtFormFields(); //获取中英字段名
        }else{
            sheet = wb.createSheet("电梯运行日常巡查记录");
            fields = getFormFields();
        }
        // 查询电梯基础数据
        ExtElevator data = elevatorMapp.queryById(elevatorId);
        int days = getDaysOfMonth(month);
        // 绘制表头
        setHeader(wb, sheet, data, days, month);

        // 获取数据
        Map<String, Object> param = new HashMap<>();
        param.put("elevatorId", elevatorId);
        param.put("month", month);
        List<Map<String, Object>> dailyData = exportMapp.queryById(param);
        List<String> dataDays = exportMapp.queryDate(param);
        // 5.3 绘制表单
        setForm(wb, sheet, fields, dailyData, dataDays, days, month);
        wb.write(fileOutStream);
        fileOutStream.flush();
        fileOutStream.close();
    }

    /**
     * 导出电梯维保记录
     * @param fileOutStream
     * @param elevatorNum
     * @param elevatorType
     * @param halfMonId
     * @throws Exception
     */
    private void writeMaintRecordToFile(FileOutputStream fileOutStream,String elevatorNum,String elevatorType,String halfMonId) throws Exception{
        // 2.创建workbook和sheet
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("电梯维护保养记录表");

        // 3.绘制表头
        String title = "电梯维护保养记录表";
        setHeader(wb, sheet,title);
        // 查询电梯基础数据
        Map<String, Object> data = exportMapp.findElevatorInfo(elevatorNum);
        // 4.绘制开头表单
        setHeaderForm(wb, sheet, data);
        // 5.绘制内部表单
        // 5.1 定义起始行号
        int rowNum = 25;
        // 5.2 获取项目
        // 5.3获取半月保数据
        Map<String, Object> maintHalfMonData = new HashMap<>();
        if ("1".equals(elevatorType) || "5".equals(elevatorType)) {
            maintHalfMonData = exportMapp.queryTractionHalfMon(halfMonId);
        } else if ("2".equals(elevatorType)) {
            maintHalfMonData = exportMapp.queryHydpressHalfMon(halfMonId);
        } else if ("3".equals(elevatorType)) {
            maintHalfMonData = exportMapp.queryDebrisHalfMon(halfMonId);
        } else if ("4".equals(elevatorType) || "6".equals(elevatorType)) {
            maintHalfMonData = exportMapp.queryEscalatorHalfMon(halfMonId);
        }
        Map<String, Object> param = new HashMap<>();
        param.put("elevatorType", elevatorType);
        param.put("maintType", "1");
        List<Map<String, Object>> fieldList = exportMapp.findFields(param);
        Map<String, Object> fields = covertListToMap(fieldList);
        List<String> items = exportMapp.findItems(param);
        List<String> demands = exportMapp.findDemands(param);
        String type = (String) maintHalfMonData.get("type");
        Integer typeInt = Integer.valueOf(type);
        // 5.4 绘制内部表单--半月保
        rowNum = setRealBody(wb, sheet, rowNum, maintHalfMonData, fields, items, demands, typeInt, 1);
        // 5.5 判断是否是季度保，若是则绘制季度保
        String quarterId = "";
        if (typeInt > 1) {
            Map<String, Object> maintData = new HashMap<>();
            if ("1".equals(elevatorType) || "5".equals(elevatorType)) {
                maintData = exportMapp.queryTractionQuarter(halfMonId);
            } else if ("2".equals(elevatorType)) {
                maintData = exportMapp.queryHydpressQuarter(halfMonId);
            } else if ("3".equals(elevatorType)) {
                maintData = exportMapp.queryDebrisQuarter(halfMonId);
            } else if ("4".equals(elevatorType) || "6".equals(elevatorType)) {
                maintData = exportMapp.queryEscalatorQuarter(halfMonId);
            }
            quarterId = String.valueOf(maintData.get("id"));
            param.put("maintType", "2");
            fieldList = exportMapp.findFields(param);
            fields = covertListToMap(fieldList);
            items = exportMapp.findItems(param);
            demands = exportMapp.findDemands(param);
            rowNum = setRealBody(wb, sheet, rowNum, maintData, fields, items, demands, typeInt, 2);
        }
        // 5.6 判断是否是半年保，若是则绘制半年保
        String halfYearId = "";
        if (typeInt > 2) {
            Map<String, Object> maintData = new HashMap<>();
            if ("1".equals(elevatorType) || "5".equals(elevatorType)) {
                maintData = exportMapp.queryTractionHalfYear(quarterId);
            } else if ("2".equals(elevatorType)) {
                maintData = exportMapp.queryHydpressHalfYear(quarterId);
            } else if ("3".equals(elevatorType)) {
                maintData = exportMapp.queryDebrisHalfYear(quarterId);
            } else if ("4".equals(elevatorType) || "6".equals(elevatorType)) {
                maintData = exportMapp.queryEscalatorHalfYear(quarterId);
            }
            halfYearId = String.valueOf(maintData.get("id"));
            param.put("maintType", "3");
            fieldList = exportMapp.findFields(param);
            fields = covertListToMap(fieldList);
            items = exportMapp.findItems(param);
            demands = exportMapp.findDemands(param);
            rowNum = setRealBody(wb, sheet, rowNum, maintData, fields, items, demands, typeInt, 3);
        }
        // 5.7 判断是否是年保，若是则绘制年保
        if (typeInt > 3) {
            Map<String, Object> maintData = new HashMap<>();
            if ("1".equals(elevatorType) || "5".equals(elevatorType)) {
                maintData = exportMapp.queryTractionYear(halfYearId);
            } else if ("2".equals(elevatorType)) {
                maintData = exportMapp.queryHydpressYear(halfYearId);
            } else if ("3".equals(elevatorType)) {
                maintData = exportMapp.queryDebrisYear(halfYearId);
            } else if ("4".equals(elevatorType) || "6".equals(elevatorType)) {
                maintData = exportMapp.queryEscalatorYear(halfYearId);
            }
            param.put("maintType", "4");
            fieldList = exportMapp.findFields(param);
            fields = covertListToMap(fieldList);
            items = exportMapp.findItems(param);
            demands = exportMapp.findDemands(param);
            rowNum = setRealBody(wb, sheet, rowNum, maintData, fields, items, demands, typeInt, 4);
        }

        // 增加维保项目
        List<MaintUserAdded> maintUserAddeds = maintUserAddedMapp.queryList(halfMonId);
        if (null != maintUserAddeds && maintUserAddeds.size() > 0) {
            XSSFCellStyle style1 = wb.createCellStyle();
            style1.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
            style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            style1.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
            style1.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
            XSSFCellStyle style2 = wb.createCellStyle();
            style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
            style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            XSSFCellStyle style3 = wb.createCellStyle();
            style3.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
            style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            style3.setWrapText(true);
            XSSFCellStyle style4 = wb.createCellStyle();
            style4.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
            style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            style4.setWrapText(true);
            XSSFCellStyle style5 = wb.createCellStyle();
            style5.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
            style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            style5.setWrapText(true);
            XSSFCellStyle style6 = wb.createCellStyle();
            style6.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
            style6.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            style6.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
            XSSFCellStyle style7 = wb.createCellStyle();
            style7.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
            style7.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
            style7.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
            style7.setWrapText(true);
            XSSFFont font1 = wb.createFont();
            font1.setFontName("宋体");
            font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            font1.setFontHeightInPoints((short) 12);
            style1.setFont(font1);
            style2.setFont(font1);
            XSSFFont font2 = wb.createFont();
            font2.setFontName("宋体");
            font2.setFontHeightInPoints((short) 12);
            style4.setFont(font2);
            style3.setFont(font2);
            style7.setFont(font2);
            XSSFFont font3 = wb.createFont();
            font3.setFontName("宋体");
            font3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            font3.setFontHeightInPoints((short) 12);
            font3.setColor(Font.COLOR_RED);
            style5.setFont(font3);
            style6.setFont(font3);

            XSSFRow smallHeadRow = sheet.createRow(rowNum);
            sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
            XSSFCell smallHead1 = smallHeadRow.createCell(0);
            smallHead1.setCellValue("增加维保项目");
            smallHead1.setCellStyle(style1);
            XSSFCell smallHead2 = smallHeadRow.createCell(1);
            smallHead2.setCellStyle(style1);
            XSSFCell smallHead3 = smallHeadRow.createCell(2);
            smallHead3.setCellStyle(style1);
            XSSFCell smallHead4 = smallHeadRow.createCell(3);
            smallHead4.setCellStyle(style1);
            rowNum += 1;

            XSSFRow smallHeadRow1 = sheet.createRow(rowNum);
            sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
            XSSFCell smallHead11 = smallHeadRow1.createCell(0);
            smallHead11.setCellValue("维保项目（内容）描述");
            smallHead11.setCellStyle(style2);
            XSSFCell smallHead21 = smallHeadRow1.createCell(1);
            smallHead21.setCellStyle(style2);
            XSSFCell smallHead31 = smallHeadRow1.createCell(2);
            smallHead31.setCellValue("维保结果");
            smallHead31.setCellStyle(style2);
            XSSFCell smallHead41 = smallHeadRow1.createCell(3);
            smallHead41.setCellValue("备注");
            smallHead41.setCellStyle(style2);
            rowNum += 1;

            for (MaintUserAdded tmp : maintUserAddeds) {
                XSSFRow rowTmp = sheet.createRow(rowNum);
                sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
                XSSFCell cellTmp1 = rowTmp.createCell(0);
                cellTmp1.setCellValue(tmp.getMaintContent());
                cellTmp1.setCellStyle(style3);
                XSSFCell cellTmp2 = rowTmp.createCell(1);
                cellTmp2.setCellStyle(style3);
                XSSFCell cellTmp3 = rowTmp.createCell(2);
                XSSFCell cellTmp4 = rowTmp.createCell(3);
                String jsonHalf = tmp.getResult();
                boolean flag = false;
                if (null != jsonHalf && !"null".equals(jsonHalf) && !"".equals(jsonHalf)) {
                    JSONObject objHalf = new JSONObject(jsonHalf);
                    if (null != objHalf.get("isOk")
                            && ((objHalf.get("isOk") instanceof String && "0".equals(objHalf.getString("isOk")))
                            || (objHalf.get("isOk") instanceof Boolean && objHalf.getBoolean("isOk")))) {
                        cellTmp3.setCellValue("√");
                        flag = true;
                    } else if (null != objHalf.get("isOk")
                            && ((objHalf.get("isOk") instanceof String && "1".equals(objHalf.getString("isOk")))
                            || (objHalf.get("isOk") instanceof Boolean && !objHalf.getBoolean("isOk")))) {
                        cellTmp3.setCellValue("×");
                    }
                    if (null != objHalf.getString("remark")) {
                        cellTmp4.setCellValue(objHalf.getString("remark"));
                    }
                }

                if (flag) {
                    cellTmp3.setCellStyle(style4);
                    cellTmp4.setCellStyle(style7);
                } else {
                    cellTmp3.setCellStyle(style5);
                    cellTmp4.setCellStyle(style6);
                }
                rowNum += 1;
            }
        }

        // 6.绘制表单结论部分
        setConclusion(wb, sheet, rowNum, maintHalfMonData);

        wb.write(fileOutStream);
        fileOutStream.flush();
        fileOutStream.close();
    }



    /**
     * 导出应急救援记录
     * @param toFilePath
     * @param id
     * @throws Exception
     */
    private void writeEmergencyRescueToFile(FileOutputStream toFilePath, String id) throws Exception{
        String xmlTemp = System.getProperty("java.io.tmpdir") + UUID.randomUUID() + ".xml";
        File temp = new File(xmlTemp);
        // xml的文件名
        String xmlTemplate = "EmergencyRescue.xml";
        // docx的路径和文件名
        String docxTemplate = environment.getProperty("TEMPLATES",String.class)+ "/EmergencyRescue.docx";
        Writer w = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(temp), "UTF-8"));
        // 1.需要动态传入的数据
        Map<String, Object> tempMap = exportMapp.findEmergencyRescue(id);
        Map<String, String> data = new HashMap<>();
        if(tempMap != null){
            Iterator it = tempMap.keySet().iterator();
            while (it.hasNext()) {
                String key = it.next()+"";
                data.put(underlineToCamel(key),tempMap.get(key) + "");
            }
        }

        System.out.println(data);
        // 2.把map中的数据动态由freemarker传给xml
        XmlFill.process(xmlTemplate, data, w,environment);

        // 3.把填充完成的xml写入到docx中
        XmlToDocx.outDocx(temp, docxTemplate, toFilePath);
        if(!temp.delete()){
            logger.warn("导出应急救援记录,删除临时文件失败");
        }
    }

    /**
     * 3.绘制表头
     *
     * @param wb
     * @param sheet
     */
    private void setHeader(XSSFWorkbook wb, XSSFSheet sheet, String title) {
        // 3.1 表头前四格合一
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        // 3.2 设置前四列宽度
        sheet.setColumnWidth(0, 25 * 256);
        sheet.setColumnWidth(1, 25 * 256);
        sheet.setColumnWidth(2, 25 * 256);
        sheet.setColumnWidth(3, 25 * 256);
        // 3.2 设置表头样式
        XSSFCellStyle headStyle = wb.createCellStyle();
        headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        headStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        headStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        headStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        headStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        headStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        XSSFFont headFont = wb.createFont();
        headFont.setFontName("宋体");
        headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        headFont.setFontHeightInPoints((short) 20);
        headStyle.setFont(headFont);
        // 3.3 获取第一行
        XSSFRow headRow = sheet.createRow(0);
        headRow.setHeightInPoints(30f); // 设置第一行高度
        // 3.4 绘制表头
        XSSFCell headerCell = headRow.createCell(0);
        XSSFCell tmp1 = headRow.createCell(1);
        tmp1.setCellStyle(headStyle);
        XSSFCell tmp2 = headRow.createCell(2);
        tmp2.setCellStyle(headStyle);
        XSSFCell tmp3 = headRow.createCell(3);
        tmp3.setCellStyle(headStyle);
        headerCell.setCellValue(title);
        headerCell.setCellStyle(headStyle);
    }

    /**
     * 4.绘制开头表单
     *
     * @param wb
     * @param sheet
     * @param data
     */
    private void setHeaderForm(XSSFWorkbook wb, XSSFSheet sheet, Map<String, Object> data) {
        // 4.1 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(5, 5, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(6, 6, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(7, 7, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(8, 8, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(9, 9, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(9, 9, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(10, 10, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(11, 11, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(11, 11, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(12, 12, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(12, 12, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(13, 13, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(13, 13, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(14, 14, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(14, 14, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(15, 15, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(15, 15, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(16, 16, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(16, 16, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(17, 17, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(17, 17, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(18, 18, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(18, 18, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(19, 19, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(19, 19, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(20, 20, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(20, 20, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(21, 21, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(22, 22, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(22, 22, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(23, 23, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(23, 23, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(24, 24, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(24, 24, 2, 3));
        // 4.2 设置样式
        // 4.2.1 第2行
        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style1.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style1.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        // 4.2.2 第3行-第24行
        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style2.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        // 4.2.3 第25行
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style3.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style3.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style3.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线

        XSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        style1.setFont(font);
        style2.setFont(font);
        style3.setFont(font);
        // 4.3 设置中英字段名
        Map<String, String> fields = getHeaderFormFields();
        String[] keys = { "电梯类型", "电梯使用单位", "电梯使用地点", "电梯注册代码", "使用单位电梯安全管理员", "电梯使用单位联系电话", "电梯维护保养单位名称",
                "电梯维护保养单位联系电话", "启用日期", "整机制造单位名称", "安装单位名称", "改造单位名称", "重大修理单位名称", "电梯产品编号", "电梯注册代码", "电梯型号",
                "改造后的型号", "电梯是否投保安全责任保险", "电梯安全责任保险投保单位名称", "电梯安全责任保险起止日期", "电梯驱动方式", "电梯额定载重量", "电梯额定速度", "电梯层数" };
        // 4.4 绘制开头表单
        for (int i = 1; i <= 24; i++) {
            XSSFRow row = sheet.createRow(i);
            row.setHeightInPoints(18f); // 设置高度
            XSSFCell cell1 = row.createCell(0);
            cell1.setCellValue(keys[i - 1]);
            XSSFCell tmp1 = row.createCell(1);
            XSSFCell cell2 = row.createCell(2);
            if (null != data.get(camelToUnderline(fields.get(keys[i - 1])))) {
                cell2.setCellValue(String.valueOf(data.get(camelToUnderline(fields.get(keys[i - 1])))));
            }
            XSSFCell tmp2 = row.createCell(3);
            if (1 == i) {
                cell1.setCellStyle(style1);
                tmp1.setCellStyle(style1);
                cell2.setCellStyle(style1);
                tmp2.setCellStyle(style1);
            } else if (24 == i) {
                cell1.setCellStyle(style3);
                tmp1.setCellStyle(style3);
                cell2.setCellStyle(style3);
                tmp2.setCellStyle(style3);
            } else {
                cell1.setCellStyle(style2);
                tmp1.setCellStyle(style2);
                cell2.setCellStyle(style2);
                tmp2.setCellStyle(style2);
            }
        }
    }

    /**
     * 5.绘制内部表单
     *
     * @param wb
     * @param sheet
     * @param rowNum
     * @param data
     * @param demands
     * @param items
     * @param fields
     * @param typeReal
     * @param typeNow
     * @throws JSONException
     */
    private int setRealBody(XSSFWorkbook wb, XSSFSheet sheet, int rowNum, Map<String, Object> data,
                            Map<String, Object> fields, List<String> items, List<String> demands, Integer typeReal, Integer typeNow)
            throws JSONException {
        // 4.1 设置样式
        // 4.1.1 第26行左
        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style1.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style1.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        // 4.1.2 第26行中
        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style2.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        // 4.1.3 第26行右
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居左
        style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style3.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style3.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        // 4.1.4 中间部分左
        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style4.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style4.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setWrapText(true);
        // 4.1.5 中间部分中
        XSSFCellStyle style5 = wb.createCellStyle();
        style5.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style5.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setWrapText(true);
        // 4.1.6 中间部分右
        XSSFCellStyle style6 = wb.createCellStyle();
        style6.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style6.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style6.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style6.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style6.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style6.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style6.setWrapText(true);
        // 4.1.7 内部表单最后一行左
        XSSFCellStyle style7 = wb.createCellStyle();
        style7.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style7.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style7.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style7.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style7.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style7.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style7.setWrapText(true);
        // 4.1.8 内部表单最后一行中
        XSSFCellStyle style8 = wb.createCellStyle();
        style8.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style8.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style8.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style8.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style8.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style8.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style8.setWrapText(true);
        // 4.1.9 内部表单最后一行右
        XSSFCellStyle style9 = wb.createCellStyle();
        style9.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style9.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style9.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style9.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style9.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style9.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style9.setWrapText(true);
        // 4.1.10 中间部分中2
        XSSFCellStyle style10 = wb.createCellStyle();
        style10.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style10.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style10.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style10.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style10.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style10.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        // 4.1.11 内部表单最后一行中2
        XSSFCellStyle style11 = wb.createCellStyle();
        style11.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style11.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style11.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style11.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style11.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style11.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        // 4.1.12 中间部分中3
        XSSFCellStyle style12 = wb.createCellStyle();
        style12.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style12.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style12.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style12.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style12.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style12.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        // 4.1.13 内部表单最后一行中3
        XSSFCellStyle style13 = wb.createCellStyle();
        style13.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style13.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style13.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style13.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style13.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style13.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        // 4.1.14 中间部分右
        XSSFCellStyle style14 = wb.createCellStyle();
        style14.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style14.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style14.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style14.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style14.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style14.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style14.setWrapText(true);
        // 4.1.15 内部表单最后一行右
        XSSFCellStyle style15 = wb.createCellStyle();
        style15.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style15.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style15.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style15.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style15.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style15.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style15.setWrapText(true);

        XSSFFont font1 = wb.createFont();
        font1.setFontName("宋体");
        font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        font1.setFontHeightInPoints((short) 12);
        style1.setFont(font1);
        style2.setFont(font1);
        style3.setFont(font1);

        XSSFFont font2 = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 12);
        style4.setFont(font2);
        style5.setFont(font2);
        style6.setFont(font2);
        style7.setFont(font2);
        style8.setFont(font2);
        style9.setFont(font2);
        style10.setFont(font2);
        style11.setFont(font2);

        XSSFFont font3 = wb.createFont();
        font3.setFontName("宋体");
        font3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        font3.setFontHeightInPoints((short) 12);
        font3.setColor(Font.COLOR_RED);
        style12.setFont(font3);
        style13.setFont(font3);
        style14.setFont(font3);
        style15.setFont(font3);

        // 4.3 绘制内部表单
        if (1 == typeNow) {
            XSSFRow smallHeadRow = sheet.createRow(rowNum);
            // smallHeadRow.setHeightInPoints(18f); // 设置高度
            XSSFCell smallHead1 = smallHeadRow.createCell(0);
            smallHead1.setCellValue("维护保养项目(内容)");
            smallHead1.setCellStyle(style1);
            XSSFCell smallHead2 = smallHeadRow.createCell(1);
            smallHead2.setCellValue("维护保养基本要求");
            smallHead2.setCellStyle(style2);
            XSSFCell smallHead3 = smallHeadRow.createCell(2);
            smallHead3.setCellValue("维保结果");
            smallHead3.setCellStyle(style2);
            XSSFCell smallHead4 = smallHeadRow.createCell(3);
            smallHead4.setCellValue("备注");
            smallHead4.setCellStyle(style3);
            rowNum += 1;
        }
        int i = 0;
        for (i = rowNum; i <= rowNum + items.size() - 1; i++) {
            XSSFRow row = sheet.createRow(i);
            // row.setHeightInPoints(18f); // 设置高度
            XSSFCell cell1 = row.createCell(0);
            cell1.setCellValue(items.get(i - rowNum));
            XSSFCell cell2 = row.createCell(1);
            cell2.setCellValue(demands.get(i - rowNum));
            XSSFCell cell3 = row.createCell(2);
            XSSFCell cell4 = row.createCell(3);
            String jsonHalf = String.valueOf(data.get(camelToUnderline((String) fields.get(items.get(i - rowNum)))));
            boolean flag = false;
            if (null != jsonHalf && !"null".equals(jsonHalf) && !"".equals(jsonHalf)) {
                JSONObject objHalf = new JSONObject(jsonHalf);
                if (null != objHalf.get("isOk")
                        && ((objHalf.get("isOk") instanceof String && "0".equals(objHalf.getString("isOk")))
                        || (objHalf.get("isOk") instanceof Boolean && objHalf.getBoolean("isOk")))) {
                    cell3.setCellValue("√");
                    flag = true;
                } else if (null != objHalf.get("isOk")
                        && ((objHalf.get("isOk") instanceof String && "1".equals(objHalf.getString("isOk")))
                        || (objHalf.get("isOk") instanceof Boolean && !objHalf.getBoolean("isOk")))) {
                    cell3.setCellValue("×");
                }
                if (null != objHalf.getString("remark")) {
                    cell4.setCellValue(objHalf.getString("remark"));
                }
            }
            if (rowNum + items.size() - 1 == i && typeReal != null && typeReal.equals(typeNow)) {
                cell1.setCellStyle(style7);
                cell2.setCellStyle(style8);
                if (flag) {
                    cell3.setCellStyle(style11);
                    cell4.setCellStyle(style9);
                } else {
                    cell3.setCellStyle(style13);
                    cell4.setCellStyle(style15);
                }
            } else {
                cell1.setCellStyle(style4);
                cell2.setCellStyle(style5);
                if (flag) {
                    cell3.setCellStyle(style10);
                    cell4.setCellStyle(style6);
                } else {
                    cell3.setCellStyle(style12);
                    cell4.setCellStyle(style14);
                }
            }
        }
        return i;
    }

    /**
     * 6.绘制表单结论部分
     *
     * @param wb
     * @param sheet
     * @param rowNum
     * @param data
     * @throws ParseException
     */
    private void setConclusion(XSSFWorkbook wb, XSSFSheet sheet, int rowNum, Map<String, Object> data)
            throws ParseException {
        // 6.1 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 1, rowNum + 1, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 1, rowNum + 1, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 2, rowNum + 2, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 2, rowNum + 2, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 3, rowNum + 3, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 3, rowNum + 3, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 4, rowNum + 4, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 4, rowNum + 4, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(rowNum + 5, rowNum + 5, 0, 3));
        // 6.2 设置样式
        // 6.2.1 结论第1行
        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style1.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style1.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        // 6.2.2 结论第2行
        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style2.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style2.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style2.setWrapText(true);
        // 6.2.3 结论第3行
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style3.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style3.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        // 6.2.4 结论第4行
        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style4.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style4.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        // 6.2.5 结论第5行
        XSSFCellStyle style5 = wb.createCellStyle();
        style5.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style5.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style5.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        // 6.2.6 结论第6行
        XSSFCellStyle style6 = wb.createCellStyle();
        style6.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style6.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style6.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style6.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style6.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); // 中等边线
        style6.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); // 中等边线

        XSSFFont font1 = wb.createFont();
        font1.setFontName("宋体");
        font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        font1.setFontHeightInPoints((short) 10.5);
        style1.setFont(font1);
        XSSFFont font2 = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 10.5);
        style2.setFont(font2);
        // 6.3 绘制结论
        XSSFRow row1 = sheet.createRow(rowNum);
        row1.setHeightInPoints(27f); // 设置高度
        XSSFCell cell11 = row1.createCell(0);
        cell11.setCellValue("调整和更换易损件等情况说明：");
        cell11.setCellStyle(style1);
        XSSFCell tmp11 = row1.createCell(1);
        tmp11.setCellStyle(style1);
        XSSFCell cell12 = row1.createCell(2);
        cell12.setCellValue("电梯使用单位意见：");
        cell12.setCellStyle(style1);
        XSSFCell tmp12 = row1.createCell(3);
        tmp12.setCellStyle(style1);

        XSSFRow row2 = sheet.createRow(rowNum + 1);
        row2.setHeightInPoints(78f); // 设置高度
        XSSFCell cell21 = row2.createCell(0);
        if (null != data.get("change_info")) {
            cell21.setCellValue(String.valueOf(data.get("change_info")));
        }
        cell21.setCellStyle(style2);
        XSSFCell tmp21 = row2.createCell(1);
        tmp21.setCellStyle(style2);
        XSSFCell cell22 = row2.createCell(2);
        if (null != data.get("used_party_opt")) {
            cell22.setCellValue(String.valueOf(data.get("used_party_opt")));
        }
        cell22.setCellStyle(style2);
        XSSFCell tmp22 = row2.createCell(3);
        tmp22.setCellStyle(style2);

        XSSFRow row3 = sheet.createRow(rowNum + 2);
        row3.setHeightInPoints(25f); // 设置高度
        XSSFCell cell31 = row3.createCell(0);
        String str31 = "电梯维保人员（签名）：";
        if (null != data.get("maintenance_user_name")) {
            str31 = str31 + String.valueOf(data.get("maintenance_user_name"));
            if(null != data.get("maintenance_user_name2")){
                str31 += ", " + String.valueOf(data.get("maintenance_user_name2"));
            }else if(null != data.get("maintenance_user_name")){
                str31 += ", " + String.valueOf(data.get("maintenance_user_name"));
            }
        }

        // 创建富文本字符串
        XSSFRichTextString richString31 = new XSSFRichTextString(str31);
        // 对前半段字体加粗
        richString31.applyFont(0, 11, font1);
        // 对后半段字体不加粗
        richString31.applyFont(11, str31.length(), font2);
        cell31.setCellValue(richString31);
        cell31.setCellStyle(style3);
        XSSFCell tmp31 = row3.createCell(1);
        tmp31.setCellStyle(style3);
        XSSFCell cell32 = row3.createCell(2);
        String str32 = "使用单位电梯安全管理员（签名）：";
        if (null != data.get("safe_user_name")) {
            str32 = str32 + String.valueOf(data.get("safe_user_name"));
        }
        // 创建富文本字符串
        XSSFRichTextString richString32 = new XSSFRichTextString(str32);
        // 对前半段字体加粗
        richString32.applyFont(0, 16, font1);
        // 对后半段字体不加粗
        richString32.applyFont(16, str32.length(), font2);
        cell32.setCellValue(richString32);
        cell32.setCellStyle(style3);
        XSSFCell tmp32 = row3.createCell(3);
        tmp32.setCellStyle(style3);

        XSSFRow row4 = sheet.createRow(rowNum + 3);
        row4.setHeightInPoints(25f); // 设置高度
        XSSFCell cell41 = row4.createCell(0);
        String str41 = "维保日期：";
        if (null != data.get("maintenance_start_date")) {
            str41 = str41 + new SimpleDateFormat("yyyy年MM月dd日").format(
                    new SimpleDateFormat("yyyyMMddHHmmss").parse(data.get("maintenance_start_date")+""));
        }
        // 创建富文本字符串
        XSSFRichTextString richString41 = new XSSFRichTextString(str41);
        // 对前半段字体加粗
        richString41.applyFont(0, 5, font1);
        // 对后半段字体不加粗
        richString41.applyFont(5, str41.length(), font2);
        cell41.setCellValue(richString41);
        cell41.setCellStyle(style4);
        XSSFCell tmp41 = row4.createCell(1);
        tmp41.setCellStyle(style4);
        XSSFCell cell42 = row4.createCell(2);
        String str42 = "确认日期：";
        if (null != data.get("safe_user_sign_time") && !"".equals(data.get("safe_user_sign_time"))) {
            str42 = str42 + new SimpleDateFormat("yyyy年MM月dd日").format(
                    new SimpleDateFormat("yyyyMMddHHmmss").parse(data.get("safe_user_sign_time")+""));
        }
        // 创建富文本字符串
        XSSFRichTextString richString42 = new XSSFRichTextString(str42);
        // 对前半段字体加粗
        richString42.applyFont(0, 5, font1);
        // 对后半段字体不加粗
        richString42.applyFont(5, str42.length(), font2);
        cell42.setCellValue(richString42);
        cell42.setCellStyle(style4);
        XSSFCell tmp42 = row4.createCell(3);
        tmp42.setCellStyle(style4);

        XSSFRow row5 = sheet.createRow(rowNum + 4);
        row5.setHeightInPoints(25f); // 设置高度
        XSSFCell cell51 = row5.createCell(0);
        String str51 = "作业开始时间：";
        if (null != data.get("maintenance_start_date")) {
            str51 = str51 + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(
                    new SimpleDateFormat("yyyyMMddHHmmss").parse(data.get("maintenance_start_date")+""));
        }
        // 创建富文本字符串
        XSSFRichTextString richString51 = new XSSFRichTextString(str51);
        // 对前半段字体加粗
        richString51.applyFont(0, 7, font1);
        // 对后半段字体不加粗
        richString51.applyFont(7, str51.length(), font2);
        cell51.setCellValue(richString51);
        cell51.setCellStyle(style5);
        XSSFCell tmp51 = row5.createCell(1);
        tmp51.setCellStyle(style5);
        XSSFCell cell52 = row5.createCell(2);
        String str52 = "作业结束时间：";
        if (null != data.get("maintenance_end_date")) {
            str52 = str52 + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(
                    new SimpleDateFormat("yyyyMMddHHmmss").parse(data.get("maintenance_end_date")+""));
        }
        // 创建富文本字符串
        XSSFRichTextString richString52 = new XSSFRichTextString(str52);
        // 对前半段字体加粗
        richString52.applyFont(0, 7, font1);
        // 对后半段字体不加粗
        richString52.applyFont(7, str52.length(), font2);
        cell52.setCellValue(richString52);
        cell52.setCellStyle(style5);
        XSSFCell tmp52 = row5.createCell(3);
        tmp52.setCellStyle(style5);

        XSSFRow row6 = sheet.createRow(rowNum + 5);
        row6.setHeightInPoints(25f); // 设置高度
        XSSFCell cell61 = row6.createCell(0);
        String str61 = "本次作业时长：";
        if (null != data.get("maintenancen_used_time")) {
            str61 = str61 + String.valueOf(data.get("maintenancen_used_time")) + "分钟";
        }
        // 创建富文本字符串
        XSSFRichTextString richString61 = new XSSFRichTextString(str61);
        // 对前半段字体加粗
        richString61.applyFont(0, 7, font1);
        // 对后半段字体不加粗
        richString61.applyFont(7, str61.length(), font2);
        cell61.setCellValue(richString61);
        cell61.setCellStyle(style6);
        XSSFCell tmp61 = row6.createCell(1);
        tmp61.setCellStyle(style6);
        XSSFCell tmp62 = row6.createCell(2);
        tmp62.setCellStyle(style6);
        XSSFCell tmp63 = row6.createCell(3);
        tmp63.setCellStyle(style6);
    }

    /**
     * 4.绘制表头
     *
     * @param wb
     * @param sheet
     * @param elevator
     * @param days
     * @param month
     */
    private void setHeader(XSSFWorkbook wb, XSSFSheet sheet, ExtElevator elevator, int days, String month) {
        // 4.1 表头前四格合一
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1 + days * 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        // 4.2 设置列宽度
        sheet.setColumnWidth(0, 44 * 256);
        for (int i = 1; i < 1 + days * 2; i = i + 2) {
            sheet.setColumnWidth(i, 5 * 256);
            sheet.setColumnWidth(i + 1, 10 * 256);
        }
        // 4.3 设置表头样式
        XSSFCellStyle headStyle1 = wb.createCellStyle();
        headStyle1.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        headStyle1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        XSSFCellStyle headStyle2 = wb.createCellStyle();
        headStyle2.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        headStyle2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style3.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        XSSFFont headFont1 = wb.createFont();
        headFont1.setFontName("宋体");
        headFont1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        headFont1.setFontHeightInPoints((short) 20);
        headStyle1.setFont(headFont1);
        XSSFFont headFont2 = wb.createFont();
        headFont2.setFontName("宋体");
        headFont2.setFontHeightInPoints((short) 11);
        headStyle2.setFont(headFont2);
        style3.setFont(headFont2);
        // 4.4 获取第一行
        XSSFRow headRow = sheet.createRow(0);
        headRow.setHeightInPoints(44f); // 设置第一行高度
        // 4.5 绘制表头
        XSSFCell headerCell = headRow.createCell(0);
        headerCell.setCellValue("电梯运行日常巡查记录");
        headerCell.setCellStyle(headStyle1);
        for (int i = 1; i < 1 + days * 2; i++) {
            XSSFCell tmp = headRow.createCell(i);
            tmp.setCellStyle(headStyle1);
        }
        // 4.6 获取第二行
        XSSFRow headRow2 = sheet.createRow(1);
        headRow2.setHeightInPoints(20.25f); // 设置第二行高度
        XSSFCell headerCell2 = headRow2.createCell(17);
        headerCell2.setCellValue("电梯编号：" + elevator.getElevatorNum());
        headerCell2.setCellStyle(headStyle2);
        XSSFCell headerCell3 = headRow2.createCell(39);
        headerCell3.setCellValue(month.substring(0, 4) + "年" + month.substring(4, 6) + "月");
        headerCell3.setCellStyle(headStyle2);
        // 4.7 获取第三行
        XSSFRow headRow3 = sheet.createRow(2);
        headRow3.setHeightInPoints(16.5f); // 设置第三行高度
        XSSFCell headerCell4 = headRow3.createCell(0);
        headerCell4.setCellValue("项目");
        headerCell4.setCellStyle(style3);
        // 4.8 获取第四行
        XSSFRow headRow4 = sheet.createRow(3);
        headRow4.setHeightInPoints(16.5f); // 设置第四行高度
        XSSFCell headerCell5 = headRow4.createCell(0);
        headerCell5.setCellStyle(style3);
        int j = 1;
        for (int i = 1; i < 1 + days * 2; i = i + 2) {
            sheet.addMergedRegion(new CellRangeAddress(2, 2, i, i + 1));
            XSSFCell tmp = headRow3.createCell(i);
            tmp.setCellValue(j + "");
            tmp.setCellStyle(style3);
            XSSFCell tmp2 = headRow3.createCell(i + 1);
            tmp2.setCellStyle(style3);
            XSSFCell tmp3 = headRow4.createCell(i);
            tmp3.setCellValue("状态");
            tmp3.setCellStyle(style3);
            XSSFCell tmp4 = headRow4.createCell(i + 1);
            tmp4.setCellValue("备注");
            tmp4.setCellStyle(style3);
            j++;
        }
    }

    /**
     * 5.绘制表单
     *
     * @param wb
     * @param sheet
     * @param fields
     * @param dailyData
     * @param dataDays
     * @param days
     * @param month
     * @throws JSONException
     */
    private void setForm(XSSFWorkbook wb, XSSFSheet sheet, Map<String, String> fields,
                         List<Map<String, Object>> dailyData, List<String> dataDays, int days, String month) throws JSONException {
        // 5.1 合并单元格
        for (int i = 1; i < 1 + days * 2; i = i + 2) {
            sheet.addMergedRegion(new CellRangeAddress(25, 25, i, i + 1));
            sheet.addMergedRegion(new CellRangeAddress(26, 26, i, i + 1));
        }
        // 5.2 填充固定值
        String[] items = { "1、轿厢内铭牌、检验标志、乘梯须知清晰", "2、轿厢内照明正常、通风良好", "3、轿厢内应急照明正常有效", "4、轿厢内紧急报警装置正常有效，与值班室通话正常",
                "5、防止门夹人保护装置动作有效", "6、开关门灵活可靠", "7、电梯运行无异常振动和响声", "8、电梯运行平层正常", "9、选层按钮正常", "10、外呼按钮正常", "11、楼层显示器正常",
                "12、侯梯厅照明正常", "13、层门无明显变形、破损", "14、层门周边无间隙、破损", "15、通道畅通、照明完好", "16、配电箱门锁完好、警示标志完好", "17、配电箱与轿厢通话正常",
                "18、控制柜无灰尘，电气元件无明显破损", "19、接触器无异响、打火花等现象。", "20、曵引机无异常噪声和振动", "21、应急救援程序清晰" };

        // 5.3 设置样式
        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style1.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setWrapText(true);
        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style2.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style3.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style4.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        XSSFCellStyle style5 = wb.createCellStyle();
        style5.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style5.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setWrapText(true);
        XSSFFont headFont1 = wb.createFont();
        headFont1.setFontName("宋体");
        headFont1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        headFont1.setFontHeightInPoints((short) 11);
        style3.setFont(headFont1);
        XSSFFont headFont2 = wb.createFont();
        headFont2.setFontName("宋体");
        headFont2.setFontHeightInPoints((short) 11);
        style1.setFont(headFont2);
        style2.setFont(headFont2);
        XSSFFont headFont3 = wb.createFont();
        headFont3.setFontName("宋体");
        headFont3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        headFont3.setFontHeightInPoints((short) 11);
        headFont3.setColor(Font.COLOR_RED);
        style4.setFont(headFont3);
        style5.setFont(headFont3);
        // 5.4 填充
        for (int i = 4; i < 25; i++) {
            XSSFRow row = sheet.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(items[i - 4]);
            cell.setCellStyle(style1);
        }
        XSSFRow row1 = sheet.createRow(25);
        XSSFCell cell2501 = row1.createCell(0);
        cell2501.setCellValue("检查总结");
        cell2501.setCellStyle(style3);
        XSSFRow row2 = sheet.createRow(26);
        XSSFCell cell2601 = row2.createCell(0);
        cell2601.setCellValue("巡查人");
        cell2601.setCellStyle(style3);
        XSSFRow row3 = sheet.createRow(27);
        XSSFCell cell2701 = row3.createCell(0);
        cell2701.setCellValue("备注：√：表示正常；Х：表示不正常。");

        for (int k = 1; k <= days; k++) {
            String date = "";
            if (k < 10) {
                date = month + "0" + k;
            } else if (k >= 10) {
                date = month + k;
            }
            if (!dataDays.contains(date)) {
                for (int j = 4; j < 25; j++) {
                    XSSFRow row = sheet.getRow(j);
                    XSSFCell cell1 = row.createCell(2 * k - 1);
                    XSSFCell cell2 = row.createCell(2 * k);
                    cell1.setCellStyle(style2);
                    cell2.setCellStyle(style1);
                }
                XSSFRow row = sheet.getRow(25);
                XSSFCell cell1 = row.createCell(2 * k - 1);
                cell1.setCellStyle(style1);
                XSSFCell cell2 = row.createCell(2 * k);
                cell2.setCellStyle(style1);
                XSSFRow row26 = sheet.getRow(26);
                XSSFCell cell261 = row26.createCell(2 * k - 1);
                cell261.setCellStyle(style1);
                XSSFCell cell262 = row26.createCell(2 * k);
                cell262.setCellStyle(style1);
                continue;
            }
            Map<String, Object> tmp = dailyData.get(dataDays.indexOf(date));
            for (int j = 4; j < 25; j++) {
                XSSFRow row = sheet.getRow(j);
                XSSFCell cell1 = row.createCell(2 * k - 1);
                XSSFCell cell2 = row.createCell(2 * k);
                String jsonHalf = String.valueOf(tmp.get(camelToUnderline((String) fields.get(items[j - 4]))));
                if (null != jsonHalf && !"null".equals(jsonHalf) && !"".equals(jsonHalf)) {
                    JSONObject objHalf = new JSONObject(jsonHalf);
                    if (null != objHalf.get("isOk")
                            && ((objHalf.get("isOk") instanceof String && "0".equals(objHalf.getString("isOk")))
                            || (objHalf.get("isOk") instanceof Boolean && objHalf.getBoolean("isOk")))) {
                        cell1.setCellValue("√");
                        cell1.setCellStyle(style2);
                        cell2.setCellStyle(style1);
                    } else if (null != objHalf.get("isOk")
                            && ((objHalf.get("isOk") instanceof String && "1".equals(objHalf.getString("isOk")))
                            || (objHalf.get("isOk") instanceof Boolean && !objHalf.getBoolean("isOk")))) {
                        cell1.setCellValue("Х");
                        cell1.setCellStyle(style4);
                        cell2.setCellStyle(style5);
                    }
                    if (null != objHalf.getString("remarks")) {
                        cell2.setCellValue(objHalf.getString("remarks"));
                    }
                }
            }
            XSSFRow row = sheet.getRow(25);
            XSSFCell cell1 = row.createCell(2 * k - 1);
            if (null != tmp.get("inspection_summary")) {
                cell1.setCellValue(String.valueOf(tmp.get("inspection_summary")));
            }
            cell1.setCellStyle(style1);
            XSSFCell cell2 = row.createCell(2 * k);
            cell2.setCellStyle(style1);
            XSSFRow row26 = sheet.getRow(26);
            XSSFCell cell261 = row26.createCell(2 * k - 1);
            if (null != tmp.get("check_user_name")) {
                cell261.setCellValue(String.valueOf(tmp.get("check_user_name")));
            }
            cell261.setCellStyle(style1);
            XSSFCell cell262 = row26.createCell(2 * k);
            cell262.setCellStyle(style1);
        }
    }

    /**
     * 5.绘制表单
     *
     * @param wb
     * @param sheet
     * @param fields
     * @param dailyData
     * @param dataDays
     * @param days
     * @param month
     * @throws JSONException
     */
    private void setFtForm(XSSFWorkbook wb, XSSFSheet sheet, Map<String, String> fields,
                           List<Map<String, Object>> dailyData, List<String> dataDays, int days, String month) throws JSONException {
        // 5.1 合并单元格
        for (int i = 1; i < 1 + days * 2; i = i + 2) {
            sheet.addMergedRegion(new CellRangeAddress(25, 25, i, i + 1));
            sheet.addMergedRegion(new CellRangeAddress(26, 26, i, i + 1));
        }
        // 5.2 填充固定值
        String[] items = { "1.运行指示灯(如果有)显示是否正常",
                "2.运行有无异常噪声、振动、剐蹭",
                "3.设备及周围的卫生是否良好",
                "4.扶手带运行速度是否正常，有无毛刺和机械损伤",
                "5.扶手栏杆和玻璃是否牢固可靠及完好无损，扶手照明(如果有)是否正常",
                "6.扶手带开口和导轨间有无异物",
                "7.梯级齿槽内有无硬质杂物",
                "8.梯级或踏板是否有缺损、裂纹，梯级或踏板之间间隙是否正常",
                "9.围裙板及内外盖板连接是否牢固，凸台或间隙有无异常",
                "10.围裙板防夹装置(如果有)是否缺损，固定是否牢靠",
                "11.梯级或踏板与围裙板间隙是否正常",
                "12.梯级或踏板是否清洁、无油污",
                "13.扶手带入口处保护装置动作是否灵活、可靠",
                "14.楼层板及梳齿支撑板是否清洁、无油污，楼层盖板是否固定可靠",
                "15.出入口是否便于疏散通畅且不被占用",
                "16.待机运行的设备，入口处的检测装置(如果有)是否可靠正常",
                "17.停止开关、钥匙启动开关功能是否正常",
                "18.梳齿板是否完好无损",
                "19.检查防爬装置(如果有)及交叉处的垂直防护挡板是否设置及完好",
                "20.检查阻挡装置和防滑装置(如果有)是否设置及完好",
                "21.检查电梯使用标志，安全警示标志是否设置完好" };

        // 5.3 设置样式
        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style1.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style1.setWrapText(true);
        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style2.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style2.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style3.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style3.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style4.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style4.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        XSSFCellStyle style5 = wb.createCellStyle();
        style5.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
        style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        style5.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderRight(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setBorderTop(XSSFCellStyle.BORDER_THIN); // 细边线
        style5.setWrapText(true);
        XSSFFont headFont1 = wb.createFont();
        headFont1.setFontName("宋体");
        headFont1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        headFont1.setFontHeightInPoints((short) 11);
        style3.setFont(headFont1);
        XSSFFont headFont2 = wb.createFont();
        headFont2.setFontName("宋体");
        headFont2.setFontHeightInPoints((short) 11);
        style1.setFont(headFont2);
        style2.setFont(headFont2);
        XSSFFont headFont3 = wb.createFont();
        headFont3.setFontName("宋体");
        headFont3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        headFont3.setFontHeightInPoints((short) 11);
        headFont3.setColor(Font.COLOR_RED);
        style4.setFont(headFont3);
        style5.setFont(headFont3);
        // 5.4 填充
        for (int i = 4; i < 25; i++) {
            XSSFRow row = sheet.createRow(i);
            XSSFCell cell = row.createCell(0);
            cell.setCellValue(items[i - 4]);
            cell.setCellStyle(style1);
        }
        XSSFRow row1 = sheet.createRow(25);
        XSSFCell cell2501 = row1.createCell(0);
        cell2501.setCellValue("检查总结");
        cell2501.setCellStyle(style3);
        XSSFRow row2 = sheet.createRow(26);
        XSSFCell cell2601 = row2.createCell(0);
        cell2601.setCellValue("巡查人");
        cell2601.setCellStyle(style3);
        XSSFRow row3 = sheet.createRow(27);
        XSSFCell cell2701 = row3.createCell(0);
        cell2701.setCellValue("备注：√：表示正常；Х：表示不正常。");

        for (int k = 1; k <= days; k++) {
            String date = "";
            if (k < 10) {
                date = month + "0" + k;
            } else if (k >= 10) {
                date = month + k;
            }
            if (!dataDays.contains(date)) {
                for (int j = 4; j < 25; j++) {
                    XSSFRow row = sheet.getRow(j);
                    XSSFCell cell1 = row.createCell(2 * k - 1);
                    XSSFCell cell2 = row.createCell(2 * k);
                    cell1.setCellStyle(style2);
                    cell2.setCellStyle(style1);
                }
                XSSFRow row = sheet.getRow(25);
                XSSFCell cell1 = row.createCell(2 * k - 1);
                cell1.setCellStyle(style1);
                XSSFCell cell2 = row.createCell(2 * k);
                cell2.setCellStyle(style1);
                XSSFRow row26 = sheet.getRow(26);
                XSSFCell cell261 = row26.createCell(2 * k - 1);
                cell261.setCellStyle(style1);
                XSSFCell cell262 = row26.createCell(2 * k);
                cell262.setCellStyle(style1);
                continue;
            }
            Map<String, Object> tmp = dailyData.get(dataDays.indexOf(date));
            for (int j = 4; j < 25; j++) {
                XSSFRow row = sheet.getRow(j);
                XSSFCell cell1 = row.createCell(2 * k - 1);
                XSSFCell cell2 = row.createCell(2 * k);
                String jsonHalf = String.valueOf(tmp.get(camelToUnderline((String) fields.get(items[j - 4]))));
                if (null != jsonHalf && !"null".equals(jsonHalf) && !"".equals(jsonHalf)) {
                    JSONObject objHalf = new JSONObject(jsonHalf);
                    if (null != objHalf.get("isOk")
                            && ((objHalf.get("isOk") instanceof String && "0".equals(objHalf.getString("isOk")))
                            || (objHalf.get("isOk") instanceof Boolean && objHalf.getBoolean("isOk")))) {
                        cell1.setCellValue("√");
                        cell1.setCellStyle(style2);
                        cell2.setCellStyle(style1);
                    } else if (null != objHalf.get("isOk")
                            && ((objHalf.get("isOk") instanceof String && "1".equals(objHalf.getString("isOk")))
                            || (objHalf.get("isOk") instanceof Boolean && !objHalf.getBoolean("isOk")))) {
                        cell1.setCellValue("Х");
                        cell1.setCellStyle(style4);
                        cell2.setCellStyle(style5);
                    }
                    if (null != objHalf.getString("remarks")) {
                        cell2.setCellValue(objHalf.getString("remarks"));
                    }
                }
            }
            XSSFRow row = sheet.getRow(25);
            XSSFCell cell1 = row.createCell(2 * k - 1);
            if (null != tmp.get("inspection_summary")) {
                cell1.setCellValue(String.valueOf(tmp.get("inspection_summary")));
            }
            cell1.setCellStyle(style1);
            XSSFCell cell2 = row.createCell(2 * k);
            cell2.setCellStyle(style1);
            XSSFRow row26 = sheet.getRow(26);
            XSSFCell cell261 = row26.createCell(2 * k - 1);
            if (null != tmp.get("check_user_name")) {
                cell261.setCellValue(String.valueOf(tmp.get("check_user_name")));
            }
            cell261.setCellStyle(style1);
            XSSFCell cell262 = row26.createCell(2 * k);
            cell262.setCellStyle(style1);
        }
    }

	/**
	 * 4.3 获取中英字段名
	 *
	 * @return
	 */
	private Map<String, String> getHeaderFormFields() {
		Map<String, String> map = new HashMap<>();
		map.put("电梯类型", "elevatorTypeId");
		map.put("电梯使用单位", "usedPartyName");
		map.put("电梯使用地点", "buildAddr");
		map.put("电梯注册代码", "elevatorNum");
		map.put("使用单位电梯安全管理员", "safeUserName");
		map.put("电梯使用单位联系电话", "usedPartyTel");
		map.put("电梯维护保养单位名称", "maintenancePartyName");
		map.put("电梯维护保养单位联系电话", "maintenancePartyTel");
		map.put("启用日期", "beUsedDate");
		map.put("整机制造单位名称", "createCompanyName");
		map.put("安装单位名称", "buildPartyName");
		map.put("改造单位名称", "");
		map.put("重大修理单位名称", "");
		map.put("电梯产品编号", "serialNum");
		map.put("电梯注册代码", "elevatorNum");
		map.put("电梯型号", "elevatorModel");
		map.put("改造后的型号", "");
		map.put("电梯是否投保安全责任保险", "insuranceNum");
		map.put("电梯安全责任保险投保单位名称", "insuranceName");
		map.put("电梯安全责任保险起止日期", "insurance_date");
		map.put("电梯驱动方式", "driveWay");
		map.put("电梯额定载重量", "angle");
		map.put("电梯额定速度", "runSpeed");
		map.put("电梯层数", "floorStation");
		return map;
	}

	/**
	 * 4.1 获取中英字段名
	 *
	 * @return
	 */
	private Map<String, String> getFormFields() {
		Map<String, String> map = new HashMap<>();
		map.put("1、轿厢内铭牌、检验标志、乘梯须知清晰", "instructionsClear");
		map.put("2、轿厢内照明正常、通风良好", "carInteriorLighting");
		map.put("3、轿厢内应急照明正常有效", "emergencyLighting");
		map.put("4、轿厢内紧急报警装置正常有效，与值班室通话正常", "emergencyAlarmDevice");
		map.put("5、防止门夹人保护装置动作有效", "doorClipHolder");
		map.put("6、开关门灵活可靠", "switchDoorFlexible");
		map.put("7、电梯运行无异常振动和响声", "runningWithoutException");
		map.put("8、电梯运行平层正常", "operationLevelNormal");
		map.put("9、选层按钮正常", "layerButtonNormal");
		map.put("10、外呼按钮正常", "callButtonNormal");
		map.put("11、楼层显示器正常", "floorMonitorNormal");
		map.put("12、侯梯厅照明正常", "staircaseLightNormal");
		map.put("13、层门无明显变形、破损", "doorNoDeformation");
		map.put("14、层门周边无间隙、破损", "gateNoGap");
		map.put("15、通道畅通、照明完好", "accessAndLighting");
		map.put("16、配电箱门锁完好、警示标志完好", "distributionBoxLocked");
		map.put("17、配电箱与轿厢通话正常", "normalCall");
		map.put("18、控制柜无灰尘，电气元件无明显破损", "controllerNoDust");
		map.put("19、接触器无异响、打火花等现象。", "contactorNoNoise");
		map.put("20、曵引机无异常噪声和振动", "machineNoException");
		map.put("21、应急救援程序清晰", "emergencyProceduresClear");
		return map;
	}

	/**
	 * 4.1 获取中英字段名
	 *
	 * @return
	 */
	private Map<String, String> getFtFormFields() {
		Map<String, String> map = new HashMap<>();
		map.put("1、运行指示灯(如果有)显示是否正常", "instructionsClear");
		map.put("2、运行有无异常噪声、振动、剐蹭", "carInteriorLighting");
		map.put("3、设备及周围的卫生是否良好", "emergencyLighting");
		map.put("4、扶手带运行速度是否正常，有无毛刺和机械损伤", "emergencyAlarmDevice");
		map.put("5、扶手栏杆和玻璃是否牢固可靠及完好无损，扶手照明(如果有)是否正常", "doorClipHolder");
		map.put("6、扶手带开口和导轨间有无异物", "switchDoorFlexible");
		map.put("7、梯级齿槽内有无硬质杂物", "runningWithoutException");
		map.put("8、梯级或踏板是否有缺损、裂纹，梯级或踏板之间间隙是否正常", "operationLevelNormal");
		map.put("9、围裙板及内外盖板连接是否牢固，凸台或间隙有无异常", "layerButtonNormal");
		map.put("10、围裙板防夹装置(如果有)是否缺损，固定是否牢靠", "callButtonNormal");
		map.put("11、梯级或踏板与围裙板间隙是否正常", "floorMonitorNormal");
		map.put("12、梯级或踏板是否清洁、无油污", "staircaseLightNormal");
		map.put("13、扶手带入口处保护装置动作是否灵活、可靠", "doorNoDeformation");
		map.put("14、楼层板及梳齿支撑板是否清洁、无油污，楼层盖板是否固定可靠", "gateNoGap");
		map.put("15、出入口是否便于疏散通畅且不被占用", "accessAndLighting");
		map.put("16、待机运行的设备，入口处的检测装置(如果有)是否可靠正常", "distributionBoxLocked");
		map.put("17、停止开关、钥匙启动开关功能是否正常", "normalCall");
		map.put("18、梳齿板是否完好无损", "controllerNoDust");
		map.put("19、检查防爬装置(如果有)及交叉处的垂直防护挡板是否设置及完好", "contactorNoNoise");
		map.put("20、检查阻挡装置和防滑装置(如果有)是否设置及完好", "machineNoException");
		map.put("21、检查电梯使用标志，安全警示标志是否设置完好", "emergencyProceduresClear");
		return map;
	}

	private Map<String, Object> covertListToMap(List<Map<String, Object>> list) {
		Map<String, Object> map = new HashMap<>();
		for (Map<String, Object> tmp : list) {
			map.put(String.valueOf(tmp.get("ITEM")), tmp.get("EN_ITEM"));
		}
		return map;
	}

	private int getDaysOfMonth(String month) throws ParseException {
		Date date = new SimpleDateFormat("yyyyMM").parse(month);
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		return calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
	}

	/**
	 * 驼峰格式字符串转换为下划线格式字符串
	 *
	 * @param param
	 * @return
	 */
	private String camelToUnderline(String param) {
		if (param == null || "".equals(param.trim())) {
			return "";
		}
		int len = param.length();
		StringBuilder sb = new StringBuilder(len);
		for (int i = 0; i < len; i++) {
			char c = param.charAt(i);
			if (Character.isUpperCase(c)) {
				sb.append("_");
				sb.append(Character.toLowerCase(c));
			} else {
				sb.append(c);
			}
		}
		return sb.toString();
	}

	/**
     * 下划线格式字符串转换为驼峰格式字符串
     *
     * @param param
     * @return
     */
	private String underlineToCamel(String param) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        param = param.toLowerCase();
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = param.charAt(i);
            if (c == '_') {
                if (++i < len) {
                    sb.append(Character.toUpperCase(param.charAt(i)));
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

}
